﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IDAL;
using Model;
using Microsoft.ApplicationBlocks.Data;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DAL
{
    public class AlbumDAL : IAlbumDAL
    {

        private static readonly string SQL_CONN_SQLSERVER = ConfigurationManager.AppSettings["DBConn"];
        private const string SQL_SELECT_ACCOUNT = "SELECT AB_albumId, AB_userId, AB_albumName, AB_authority,AB_generateTime,AB_description,AB_photoCount FROM Album WHERE AB_albumId = @albumId";
        private const string SQL_SELECT_ACCOUNT1 = "SELECT * FROM Album WHERE AB_userId = @userId";
        private const string SQL_SELECT_ALBUMNAME1 = "SELECT AB_albumId, AB_userId, AB_albumName, AB_authority,AB_generateTime,AB_description,AB_photoCount FROM Album WHERE AB_albumName LIKE @albumName";
        /// <summary>
        /// 根据传入的albumId判断一条数据是否存在
        /// </summary>       
        public bool albumIdExists(int albumId)
        {
            bool isExist = false;
            string SQL_SELECT_ACCOUNT = "SELECT * FROM Album WHERE AB_albumId = @albumId";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@albumId", albumId);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_ACCOUNT, para))
                {
                    if (rdr.Read())
                        isExist = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return isExist;

        }

        /// <summary>
        /// 根据传入的albumname判断一条数据是否存在
        /// </summary>       
        public bool albumNameExists(string albumName)
        {
            bool isExist = false;
            string SQL_SELECT_ACCOUNT = "SELECT * FROM Album WHERE AB_albumName = @albumName";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@albumName", albumName);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_ACCOUNT, para))
                {
                    if (rdr.Read())
                        isExist = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return isExist;

        }


        /// <summary>
        /// 根据传入的userId和albumname判断相册名是否重复
        /// </summary>       
        public bool nameDuplicate(int userId, string albumName) {
            bool isDuplicate = false;
            string SQL_SELECT_ACCOUNT = "SELECT * FROM Album WHERE AB_userId = @userId AND AB_albumName = @albumName";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter[] paras;
                paras = new SqlParameter[] { new SqlParameter("@userId", userId), new SqlParameter("@albumName", albumName) };

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_ACCOUNT, paras))
                {
                    if (rdr.Read())
                        isDuplicate = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return isDuplicate;
        }


        /// <summary>
        /// 根据传入的userID判断一条数据是否存在
        /// </summary>       
        public bool userExists(int userId)
        {
            bool isExist = false;
            string SQL_SELECT_ACCOUNT = "SELECT * FROM Album WHERE AB_userId = @userId";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@userId", userId);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_ACCOUNT, para))
                {
                    if (rdr.Read())
                        isExist = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return isExist;

        }


        /// <summary>
        /// 增加一条数据
        /// </summary>                
        public bool Add(int userId, string albumName, int authority, System.DateTime generateTime, string description, int photoCount)
        {
            bool opetation = false;
            string sqlStatement = "INSERT INTO Album VALUS( @userId, @albumName, @authority,@generateTime, @description,@photoCount )";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter[] paras;
                paras = new SqlParameter[] { new SqlParameter("@userId", userId), new SqlParameter("@albumName", albumName), new SqlParameter("@authority", authority), new SqlParameter("@generateTime", generateTime), new SqlParameter("@description", description), new SqlParameter("@photoCount", photoCount) };

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, paras);
                {
                    if (lineNum > 0)
                        opetation = true;
                }

                /// <summary>
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>                
        public bool Add(Model.Album album)
        {
            bool opetation = false;
            string sqlStatement = "INSERT INTO Album VALUES( @userId, @albumName, @authority,@generateTime, @description,@photoCount)";
            Model.Album albums = album;

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter[] paras;
                paras = new SqlParameter[] { new SqlParameter("@userId", albums.getAB_userId()), new SqlParameter("@albumName", albums.getAB_albumName()), new SqlParameter("@authority", albums.getAB_authority()), new SqlParameter("@generateTime", albums.getAB_generateTime()), new SqlParameter("@description", albums.getAB_description()), new SqlParameter("@photoCount", albums.getAB_photoCount()) };

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, paras);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>             
        public bool Update(int albumId, int userId, string albumName, int authority, System.DateTime generateTime, string description, int photoCount)
        {
            //Set up a return value
            bool opetation = false;
            string sqlStatement = "UPDATE Album SET  AB_albumName = @albumName, AB_authority=@authority,AB_generateTime=@generateTime,AB_description=@description,AB_photoCount=@photoCount WHERE AB_albumId=@albumId";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter[] paras;
                paras = new SqlParameter[] { new SqlParameter("@albumName", albumName), new SqlParameter("@authority", authority), new SqlParameter("@generateTime", generateTime), new SqlParameter("@description", description), new SqlParameter("@photoCount", photoCount), new SqlParameter("@albumId", albumId) };

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, paras);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>             
        public bool Update(Model.Album album)
        {
            //Set up a return value
            bool opetation = false;
            string sqlStatement = "UPDATE Album SET AB_albumName = @albumName, AB_authority = @authority,AB_generateTime=@generateTime,AB_description=@description,AB_photoCount=@photoCount WHERE AB_albumId=@albumId";
            Model.Album albums = album;

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter[] paras;
                paras = new SqlParameter[] { new SqlParameter("@albumName", albums.getAB_albumName()), new SqlParameter("@authority", albums.getAB_authority()), new SqlParameter("@generateTime", albums.getAB_generateTime()), new SqlParameter("@description", albums.getAB_description()), new SqlParameter("@photoCount", albums.getAB_photoCount()), new SqlParameter("@albumId", albums.getAB_albumId()) };

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, paras);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }

        /// <summary>
        /// 根据传入的albumId删除一条数据
        /// </summary>
        public bool albumDelete(int albumId)
        {
            bool opetation = false;
            string sqlStatement = "DELETE  FROM Album WHERE AB_albumId = @albumId";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@albumId", albumId);

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, para);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }


        /// <summary>
        /// 根据传入的userID删除一条数据
        /// </summary>
        public bool userDelete(int userId)
        {
            bool opetation = false;
            string sqlStatement = "DELETE FROM Album WHERE AB_userId = @userId";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@userId", userId);

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, para);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }


        /// <summary>
        /// 根据传入的albumId得出相册内照片数
        /// </summary>
        public int getCount(int albumId)
        {
            int album = 0;
            string SQL_SELECT_ACCOUNT = "SELECT * FROM Album WHERE AB_albumId = @albumId";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@albumId", albumId);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_ACCOUNT, para))
                {
                    if (rdr.Read())
                        album = Convert.ToInt32(rdr.GetValue(6).ToString());
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return album;

        }
        /// <summary>
        /// 根据传入的albumId得到相册访问权限
        /// </summary>
        public int getAuthority(int albumId)
        {
            int authority = 0;
            string SQL_SELECT_ACCOUNT = "SELECT * FROM Album WHERE AB_albumId = @albumId";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@albumId", albumId);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_ACCOUNT, para))
                {
                    if (rdr.Read())
                        authority = Convert.ToInt32(rdr.GetInt32(3));
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return authority;

        }

        /// <summary>
        /// 根据传入的albumId得出相册描述
        /// </summary>
        public string getDescription(int albumId)
        {
            string description = null;
            string SQL_SELECT_ACCOUNT = "SELECT * FROM Album WHERE AB_albumId = @albumId";

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@albumId", albumId);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_ACCOUNT, para))
                {
                    if (rdr.Read())
                        description = rdr.GetValue(5).ToString();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return description;

        }

        /// <summary>
        /// 根据albumId得到一个对象实体
        /// </summary>
        public Model.Album GetModel(int albumId)
        {
            Model.Album album = null;   

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@albumId", albumId);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_ACCOUNT, para))
                {
                    if (rdr.Read())

                        album = new Model.Album(Convert.ToInt32(rdr.GetInt32(0)), Convert.ToInt32(rdr.GetInt32(1)), rdr.GetString(2), Convert.ToInt32(rdr.GetInt32(3)), Convert.ToDateTime(rdr.GetDateTime(4)), rdr.GetString(5), Convert.ToInt32(rdr.GetInt32(6)));
                    else
                        album = new Model.Album();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return album;
        }

        /// <summary>
        /// 根据userId得到相册列表
        /// </summary>
        public List<Model.Album> GetList(int userId) {
            List<Model.Album> list = new List<Model.Album>();

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                para = new SqlParameter("@userId", userId);

                //Execute the query	
                SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_ACCOUNT1, para);
                
                    while (rdr.Read())
                    {
                        Model.Album album;
                        album = new Model.Album(Convert.ToInt32(rdr.GetInt32(0)), Convert.ToInt32(rdr.GetInt32(1)), rdr.GetString(2), Convert.ToInt32(rdr.GetInt32(3)), Convert.ToDateTime(rdr.GetDateTime(4)), rdr.GetString(5), Convert.ToInt32(rdr.GetInt32(6)));
                    
                        list.Add(album);
                    }
                
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return list;
        }


        /// <summary>
        /// 根据albumName得到相册列表
        /// </summary>
        public List<Model.Album> GetListByName(string albumName) {
            List<Model.Album> list = new List<Model.Album>();

            try
            {
                SqlConnection conn = new SqlConnection(SQL_CONN_SQLSERVER);

                SqlParameter para;
                string AlbumName = "%" + albumName + "%";
                para = new SqlParameter("@albumName", AlbumName);

                //Execute the query	
                SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, SQL_SELECT_ALBUMNAME1, para);

                while (rdr.Read())
                {
                    Model.Album album;
                    album = new Model.Album(Convert.ToInt32(rdr.GetInt32(0)), Convert.ToInt32(rdr.GetInt32(1)), rdr.GetString(2), Convert.ToInt32(rdr.GetInt32(3)), Convert.ToDateTime(rdr.GetDateTime(4)), rdr.GetString(5), Convert.ToInt32(rdr.GetInt32(6)));

                    list.Add(album);
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return list;
        
        }
    }
}
